<?php
class Activity{
    protected $db;
    protected $type;

    protected $months;
    protected $cities;
    protected $userCities;
    protected $calls;
    protected $meetings;
    protected $opportunities;
    protected $userCitySummaryData;
    protected $citySummaryData;
    protected $citySummarySum;
    protected $monthData;
    public $allMC = 0;
    public $allO = 0;
    public $collspan = 0;
    public $from;
    public $to;
    public $noServiceLabel = '';

    public function __construct($type = 'city'){
        $this->db = $GLOBALS['db'];
        $this->type = $type;
    }

    public function getCities(){
        if(!is_array($this->cities)){
            if($this->type == 'city'){
                $sql = "SELECT gc.id, gc.name FROM grp_cities gc WHERE gc.deleted = 0";
                $this->cities = $this->getAllRows($sql);
            }else{
                global $app_list_strings;
                $services = $app_list_strings['gruper_customer_service_type_list'];
                $this->cities = array();
                foreach($services as $key => $val){
                    $position = strpos($val, '(');
                    if($position){
                        $valTemp = substr($val, 0, $position);
                    }else{
                        $valTemp = $val;
                    }
                    if(empty($key) && empty($val)){
                        $valTemp = $this->noServiceLabel;
                    }
                    $this->cities[] = array('id' => $key, 'name' => $valTemp);
                }
            }
        }
        return $this->cities;
        
    }
    /**
     * Fetches all cities, which user is assigned to
     * @return array
     */
    public function getUserCities(){
        if(!is_array($this->userCities)){
            if($this->type == 'city'){
                $sql = "SELECT DISTINCT CONCAT(u.last_name, ' ' ,IFNULL(u.first_name, '')) as userName, u.id as userId, gcu.grp_city_id as city_id
                    FROM users u
                    INNER JOIN grp_cities_users gcu ON gcu.user_id = u.id
                    WHERE u.deleted = 0 AND gcu.deleted = 0
                    ORDER BY u.last_name, u.first_name";
                $this->userCities = $this->getAllRows($sql);
            }else{
                $this->userCities = $this->getUsersForService();
            }
        }
        return $this->userCities;
    }
    
    

    public function createMonthsArr($from, $to){
        if(!is_array($this->months)){
            $this->months = array();
            $start = explode('-', $from);
            $startYear = (int)$start[0];
            $startMonth = (int)$start[1];

            $end = explode('-', $to);
            $endYear = (int)$end[0];
            $endMonth = (int)$end[1];

            while($startYear != $endYear || $startMonth != $endMonth + 1){
                $this->months[] = array('title' => $startYear.'-'.$startMonth,'id' => $startYear.'-'.$startMonth);
                if($startMonth == 12){
                    $startMonth = 1;
                    $startYear++;
                }else{
                    $startMonth++;
                }
                $this->collspan++;
            }
        }

        return $this->months;
    }

    public function createData($from, $to){
        $data = array();
        $this->userCitySummaryData = array();
        $this->citySummaryData = array();
        $this->citySummarySum = array();
        $this->monthData = array();
        $this->prepareCitySummaryData($from, $to);
        
        foreach($this->getCities() as $city){
            foreach($this->getUserCities() as $userCity){
                if($userCity['city_id'] == $city['id']){
                    foreach($this->createMonthsArr($from, $to) as $month){
                        $temp = array(
                                'userId' => $userCity['userId'],
                                'city_id' => $city['id'],
                                'yearMonth' => $month['id'],
                                'm' => $this->findMeeting($from, $to, $city['id'], $userCity['userId'], $month['id']),
                                'c' => $this->findCall($from, $to, $city['id'], $userCity['userId'], $month['id']),
                                'mc' => 0,
                                'o' => $this->findOpportunity($from, $to, $city['id'], $userCity['userId'], $month['id']),
                            );
                        $temp['mc'] = $temp['c'] + $temp['m'];
                        $data[] = $temp;
                        
                        $userSummaryKey = $this->findUserCitySummaryData($userCity['userId'], $city['id']);
                        if($userSummaryKey !== false){
                            $this->userCitySummaryData[$userSummaryKey]['mc'] += $temp['mc'];
                            $this->userCitySummaryData[$userSummaryKey]['o'] += $temp['o'];
                        }else{
                            $this->userCitySummaryData[] = array(
                                'city_id' => $city['id'],
                                'userId' => $userCity['userId'],
                                'mc' => $temp['mc'],
                                'o' => $temp['o'],
                                );
                        }

                        $citySummaryKey = $this->findCitySummaryData($city['id'], $month['id']);
                        if($citySummaryKey !== false){
                            $this->citySummaryData[$citySummaryKey]['m'] += $temp['m'];
                            $this->citySummaryData[$citySummaryKey]['c'] += $temp['c'];
                            $this->citySummaryData[$citySummaryKey]['mc'] += $temp['mc'];
                            $this->citySummaryData[$citySummaryKey]['o'] += $temp['o'];
                        }else{
                            $this->citySummaryData[] = array(
                                'city_id' => $city['id'],
                                'yearMonth' => $month['id'],
                                'm' => $temp['m'],
                                'c' => $temp['c'],
                                'mc' => $temp['mc'],
                                'o' => $temp['o'],
                                );
                        }

                        $citySummarySumKey = $this->findCitySummarySum($city['id']);
                        if($citySummarySumKey !== false){
                            $this->citySummarySum[$citySummarySumKey]['mc'] += $temp['mc'];
                            $this->citySummarySum[$citySummarySumKey]['o'] += $temp['o'];
                        }else{
                            $this->citySummarySum[] = array(
                                'city_id' => $city['id'],
                                'mc' => $temp['mc'],
                                'o' => $temp['o'],
                            );
                        }

                        $monthDataKey = $this->findMonthData($month['id']);
                        if($monthDataKey !== false){
                            $this->monthData[$monthDataKey]['m'] += $temp['m'];
                            $this->monthData[$monthDataKey]['c'] += $temp['c'];
                            $this->monthData[$monthDataKey]['mc'] += $temp['mc'];
                            $this->monthData[$monthDataKey]['o'] += $temp['o'];
                        }else{
                            $this->monthData[] = array(
                                'yearMonth' => $month['id'],
                                'm' => $temp['m'],
                                'c' => $temp['c'],
                                'mc' => $temp['mc'],
                                'o' => $temp['o'],
                            );
                        }
                        $this->allMC += $temp['mc'];
                        $this->allO += $temp['o'];
                    }
                }
            }
        }
        return $data;
    }

    protected function findMonthData($yearMonth){
        if(!empty($this->monthData)){
            foreach($this->monthData as $key => $month){
                if($month['yearMonth'] == $yearMonth){
                    return $key;
                }
            }
        }
        return false;
    }

    protected function findCitySummarySum($city_id){
        if(!empty($this->citySummarySum)){
            foreach($this->citySummarySum as $key => $citySummary){
                if($citySummary['city_id'] == $city_id){
                    return $key;
                }
            }
        }
        return false;
    }

    protected function findUserCitySummaryData($userId, $city_id){
        if(!empty($this->userCitySummaryData)){
            foreach($this->userCitySummaryData as $key => $userSummary){
                if($userSummary['city_id'] == $city_id && $userSummary['userId'] == $userId){
                    return $key;
                }
            }
        }
        return false;
    }

    protected function findCitySummaryData($city_id, $yearMonth){
        if(!empty($this->citySummaryData)){
            foreach($this->citySummaryData as $key => $citySummary){
                if($citySummary['city_id'] == $city_id && $citySummary['yearMonth'] == $yearMonth){
                    return $key;
                }
            }
        }
        return false;
    }

    public function getUserCitySummaryData(){
        return $this->userCitySummaryData;
    }

    public function getCitySummaryData(){
        return $this->citySummaryData;
    }

    public function getCitySummarySum(){
        return $this->citySummarySum;
    }

    public function getMonthData(){
        return $this->monthData;
    }

    protected function findCall($from, $to, $city_id, $userId, $yearMonth){
        foreach($this->getUserCallsData($from, $to) as $call){
            if($call['city_id'] == $city_id && $call['userId'] == $userId && $call['yearMonth'] == $yearMonth){
                return $call['c'];
            }
        }
        return 0;
    }

    protected function findMeeting($from, $to, $city_id, $userId, $yearMonth){
        foreach($this->getUserMeetingsData($from, $to) as $meeting){
            if($meeting['city_id'] == $city_id && $meeting['userId'] == $userId && $meeting['yearMonth'] == $yearMonth){
                return $meeting['m'];
            }
        }
        return 0;
    }

    protected function findOpportunity($from, $to, $city_id, $userId, $yearMonth){
        foreach($this->getUserOpportunitiesData($from, $to) as $opp){
            if($opp['city_id'] == $city_id && $opp['userId'] == $userId && $opp['yearMonth'] == $yearMonth){
                return $opp['o'];
            }
        }
        return 0;

    }

    public function getUserCallsData($from, $to){
        if(!is_array($this->calls)){
            if($this->type == 'city'){
                $sql = "SELECT count(*) as c, CONCAT(YEAR(c.date_entered), '-', MONTH(c.date_entered)) as yearMonth,
                        a.city_id as city_id, u.id as userId
                            FROM `calls` c
                            INNER JOIN users u ON c.created_by = u.id
                            INNER JOIN accounts_contacts ac ON c.parent_id = ac.contact_id
                            INNER JOIN accounts a ON a.id = ac.account_id
                            INNER JOIN grp_cities gc ON a.city_id = gc.id
                            WHERE c.date_entered >= '$this->from 00:00:00' AND c.date_entered <= '$this->to 23:59:59'
                            AND c.deleted=0
                            AND a.deleted = 0
                            AND ac.deleted = 0
                            GROUP BY c.created_by, YEAR(c.date_entered), MONTH(c.date_entered), a.city_id ";
            }else{
                $sql = "SELECT count(*) as c, CONCAT(YEAR(c.date_entered), '-', MONTH(c.date_entered)) as yearMonth,
                    acstm.gruper_customer_service_type_c  as city_id, u.id as userId
                        FROM `calls` c
                        INNER JOIN users u ON c.created_by = u.id
                        INNER JOIN accounts_contacts ac ON c.parent_id = ac.contact_id
                        INNER JOIN accounts a ON a.id = ac.account_id
                        INNER JOIN accounts_cstm acstm ON a.id = acstm.id_c
                        WHERE c.date_entered >= '$this->from 00:00:00' AND c.date_entered <= '$this->to 23:59:59'
                        AND c.deleted=0
                        AND a.deleted = 0
                        AND ac.deleted = 0
                        GROUP BY c.created_by, YEAR(c.date_entered), MONTH(c.date_entered), acstm.gruper_customer_service_type_c ";
            }
            $this->calls = $this->getAllRows($sql);
        }
        //echo $sql.'<br>';
        return $this->calls;
    }

    public function getUserMeetingsData($from, $to){
        if(!is_array($this->meetings)){
            if($this->type == 'city'){
                $sql = "SELECT count(*) as m, CONCAT(YEAR(m.date_entered), '-', MONTH(m.date_entered)) as yearMonth,
                        a.city_id as city_id, u.id as userId
                            FROM meetings m
                            INNER JOIN users u ON m.created_by = u.id
                            INNER JOIN accounts_contacts ac ON m.parent_id = ac.contact_id
                            INNER JOIN accounts a ON a.id = ac.account_id
                            INNER JOIN grp_cities gc ON a.city_id = gc.id
                            WHERE m.date_entered >= '$this->from 00:00:00' AND m.date_entered <= '$this->to 23:59:59'
                            AND m.deleted = 0
                            AND a.deleted = 0
                            AND ac.deleted = 0
                            GROUP BY m.created_by, YEAR(m.date_entered), MONTH(m.date_entered), a.city_id ";
            }else{
                $sql = "SELECT count(*) as m, CONCAT(YEAR(m.date_entered), '-', MONTH(m.date_entered)) as yearMonth,
                        acstm.gruper_customer_service_type_c as city_id, u.id as userId
                        FROM meetings m
                        INNER JOIN users u ON m.created_by = u.id
                        INNER JOIN accounts_contacts ac ON m.parent_id = ac.contact_id
                        INNER JOIN accounts a ON a.id = ac.account_id
                        INNER JOIN accounts_cstm acstm ON a.id = acstm.id_c
                        WHERE m.date_entered >= '$this->from 00:00:00' AND m.date_entered <= '$this->to 23:59:59'
                        AND m.deleted = 0
                        AND a.deleted = 0
                        AND ac.deleted = 0
                        GROUP BY m.created_by, YEAR(m.date_entered), MONTH(m.date_entered), acstm.gruper_customer_service_type_c ";
            }
            $this->meetings = $this->getAllRows($sql);
           // echo $sql;
        }
        return $this->meetings;
    }

    public function getUserOpportunitiesData($from, $to){
        if(!is_array($this->opportunities)){
            if($this->type == 'city'){
                $sql = "SELECT count(*) as o, CONCAT(YEAR(o.date_entered), '-', MONTH(o.date_entered)) as yearMonth, a.city_id as city_id, u.id as userId
                        FROM opportunities o
                        INNER JOIN users u ON o.created_by = u.id
                        INNER JOIN accounts_opportunities ao ON o.id = ao.opportunity_id
                        INNER JOIN accounts a ON a.id = ao.account_id
                        WHERE o.date_entered >= '$this->from 00:00:00' AND o.date_entered <= '$this->to 23:59:59'
                        AND o.sales_stage = 'Closed Won'
                        AND o.deleted = 0
                        AND a.deleted = 0
                        AND ao.deleted = 0
                        GROUP BY o.created_by, YEAR(o.date_entered), MONTH(o.date_entered), a.city_id ";
            }else{
                $sql = "SELECT count(*) as o, CONCAT(YEAR(o.date_entered), '-', MONTH(o.date_entered)) as yearMonth, acstm.gruper_customer_service_type_c as city_id, u.id as userId
                    FROM opportunities o
                    INNER JOIN users u ON o.created_by = u.id
                    INNER JOIN accounts_opportunities ao ON o.id = ao.opportunity_id
                    INNER JOIN accounts a ON a.id = ao.account_id
                    INNER JOIN accounts_cstm acstm ON a.id = acstm.id_c
                    WHERE o.date_entered >= '$this->from 00:00:00' AND o.date_entered <= '$this->to 23:59:59'
                    AND o.sales_stage = 'Closed Won'
                    AND o.deleted = 0
                    AND a.deleted = 0
                    AND ao.deleted = 0
                    GROUP BY o.created_by, YEAR(o.date_entered), MONTH(o.date_entered), acstm.gruper_customer_service_type_c ";
            }
            //echo $sql;
            $this->opportunities = $this->getAllRows($sql);

        }
        return $this->opportunities;
    }
    protected function getAllRows($sql){
        $rows = array();
        $result = $this->db->query($sql);
        while($row = $this->db->fetchByAssoc($result) ){
            $rows[] = $row;
        }
        
        return $rows;
    }

    //SERVICE SECTION
    protected function getUsersForService(){
        $sqlCall = "SELECT acstm.gruper_customer_service_type_c as city_id, u.id as userId, CONCAT(u.last_name, ' ' ,IFNULL(u.first_name, '')) as userName
                FROM `calls` c
                INNER JOIN users u ON c.created_by = u.id
                INNER JOIN accounts_contacts ac ON c.parent_id = ac.contact_id
                INNER JOIN accounts a ON a.id = ac.account_id
                INNER JOIN accounts_cstm acstm ON a.id = acstm.id_c
                WHERE c.date_entered >= '$this->from 00:00:00' AND c.date_entered <= '$this->to 23:59:59'
                AND c.deleted=0
                AND a.deleted = 0
                AND ac.deleted = 0
                GROUP BY  c.created_by,acstm.gruper_customer_service_type_c ";

        $sqlMeeting = "SELECT acstm.gruper_customer_service_type_c as city_id, u.id as userId, CONCAT(u.last_name, ' ' ,IFNULL(u.first_name, '')) as userName
                FROM meetings m
                INNER JOIN users u ON m.created_by = u.id
                INNER JOIN accounts_contacts ac ON m.parent_id = ac.contact_id
                INNER JOIN accounts a ON a.id = ac.account_id
                INNER JOIN accounts_cstm acstm ON a.id = acstm.id_c
                WHERE m.date_entered >= '$this->from 00:00:00' AND m.date_entered <= '$this->to 23:59:59'
                AND m.deleted = 0
                AND a.deleted = 0
                AND ac.deleted = 0
                GROUP BY  m.created_by, acstm.gruper_customer_service_type_c ";

        $sqlOpp = "SELECT acstm.gruper_customer_service_type_c as city_id, u.id as userId, CONCAT(u.last_name, ' ' ,IFNULL(u.first_name, '')) as userName
                    FROM opportunities o
                    INNER JOIN users u ON o.created_by = u.id
                    INNER JOIN accounts_opportunities ao ON o.id = ao.opportunity_id
                    INNER JOIN accounts a ON a.id = ao.account_id
		    INNER JOIN accounts_cstm acstm ON a.id = acstm.id_c
                    WHERE o.date_entered >= '$this->from 00:00:00' AND o.date_entered <= '$this->to 23:59:59'
                    AND o.deleted = 0
                    AND a.deleted = 0
                    AND ao.deleted = 0
                    GROUP BY o.created_by, acstm.gruper_customer_service_type_c";

          $sql = $sqlCall." UNION ".$sqlMeeting." UNION ".$sqlOpp;
          $usersServiceType = $this->getAllRows($sql);

        return $usersServiceType;
    }

    protected function prepareCitySummaryData($from, $to){
        foreach($this->getCities() as $city){
                    foreach($this->createMonthsArr($from, $to) as $month){
                         $this->citySummaryData[] = array(
                                'city_id' => $city['id'],
                                'yearMonth' => $month['id'],
                                'm' => 0,
                                'c' => 0,
                                'mc' => 0,
                                'o' => 0,
                                );

                        $citySummarySumKey = $this->findCitySummarySum($city['id']);
                        if($citySummarySumKey !== false){
                            $this->citySummarySum[$citySummarySumKey]['mc'] = 0;
                            $this->citySummarySum[$citySummarySumKey]['o'] = 0;
                        }else{
                            $this->citySummarySum[] = array(
                                'city_id' => $city['id'],
                                'mc' => 0,
                                'o' => 0,
                            );
                        }
                    }
        }
    }
    //END

    public function hasUserAccessToActivity($userId){
        if( isset($_SESSION['ACL'][$userId]['activity_access'])){
            if(!is_null($_SESSION['ACL'][$userId]['activity_access'])){
               return $_SESSION['ACL'][$userId]['activity_access'];
            }
        }
        $userRolesArr = ACLRole::getUserRoles($userId, false);

        if(!empty($userRolesArr)){
            foreach($userRolesArr as $userRole){
                if($userRole->name == 'Sales Dyrector' || $userRole->name == 'regional director' || $userRole->id == '196cfbd4-8e36-f56a-c927-4be08e5728fa' || $userRole->id == 'e76bb993-43e0-ab74-dd18-4c9c81ffffe3'){
                    $_SESSION['ACL'][$userId]['activity_access'] = true;
                    return true;
                }
            }
        }

        $_SESSION['ACL'][$userId]['activity_access'] = false;
        return false;
    }
}